#!/usr/bin/perl
# wattsclever4linux <http://code.google.com/p/wattscleverlogger4linux/>
#   ltd@Interlink.com.au
#   license at http://www.gnu.org/licenses/old-licenses/gpl-2.0.html

# this is the cgi helper script to provide access to the collected
# (realtime and historic) data

use POSIX;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use IPC::ShareLite;
use Storable qw( freeze thaw );
use POSIX qw(strftime);
use Data::Dumper;

require "collector/settings.inc";

$| = 1;
$query = new CGI;

$device = $query->param("d");			# device is 1,2,3  defaults to 1 if not specified
$charttype = $query->param("charttype");	# detailed/hourly/daily/realtime
$stime = $query->param("s");			# start time
$etime = $query->param("e");			# end time

$device = "1" if ($device eq "");
$stime = 0 if ($stime eq "");			# start time is early if not specified
$etime = time if ($etime eq "");		# end time is 'now' if not specified

# if no charttype is specified, choose one based on time history requested
if ($charttype eq "") {
	my $timerange = $etime - $stime;
	if ($timerange <= (6 * 3600)) {
		$charttype = "detailed";
	} elsif ($timerange <= (6 * 24 * 3600)) {
		$charttype = "hourly";
	} else {
		$charttype = "daily";
	}
}

if ($charttype eq "detailed") {
	$sql = 'SELECT date_time AS A, energy, temperature FROM instantaneous_readings'.
		' WHERE device_id = '.$device.' AND date_time >= '.$stime.' AND date_time <= '.$etime.
		' ORDER BY date_time ASC';
} elsif ($charttype eq "hourly") {
	$sql = 'SELECT date_time AS A, energy, temperature FROM hourly_readings'.
		' WHERE device_id = '.$device.' AND date_time >= '.$stime.' AND date_time <= '.$etime.
		' ORDER BY date_time ASC';
} elsif ($charttype eq "daily") {
	$sql = 'SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(date_time,"%Y-%m-%d")) AS A, SUM(energy) AS energy,'.
		' AVG(temperature) AS temperature, MIN(temperature) AS mintemp, MAX(temperature) AS maxtemp FROM hourly_readings'.
		' WHERE device_id = '.$device.' AND date_time >= '.$stime.' AND date_time <= '.$etime.
		' GROUP BY A ORDER BY date_time ASC';
} elsif ($charttype eq "realtime") {
	;
} else {
	printf "unknown chart type '%s' specified, can be one of detailed/hourly/daily/realtime\n",$charttype;
}

printf "Content-type: application/json\n\n[";

if ($charttype ne "realtime") {
	#
	# print output from historical database
	#

	#printf STDERR "%s sql: %s\n",$charttype,$sql;
	$dbh = DBI->connect("dbi:mysql:database=energy:host=localhost", $db_user, $db_pass) || die "Cannot connect to database: $!\n\n";
	$list_query = $dbh->prepare($sql);
	if (!($list_query->execute())) {
		printf "SQL error: query returned error %s\n",$list_query->errstr;
		exit(0);
	}

	for ($i=0; $i < $list_query->rows; $i++) {
		my $qr = $list_query->fetchrow_hashref();
		printf "%s[%s,%0.4f,%0.2f%s%s]",
			($i > 0 ? ",\n " : ""),
			$qr->{A},
			$qr->{energy},
			$qr->{temperature},
			(defined $qr->{mintemp} ? sprintf ",%0.1f",$qr->{mintemp} : ""),
			(defined $qr->{maxtemp} ? sprintf ",%0.1f",$qr->{maxtemp} : "");
	}
} else {
	#
	# show stack of latest realtime readings
	#

	my $shared_mem = IPC::ShareLite->new(-key => 6666, -create  => 'no', -destroy => 'no');
	if (!defined $shared_mem) {
		printf STDERR "could not open shared memory: $!\n";
	} else {
		my $realtime = thaw($shared_mem->fetch);
		my $existing_readings = 0;
		while (my $r = pop @{$realtime}) {
			if (($r->{timestamp} > $stime) && ($r->{timestamp} <= $etime)) {
				printf "%s[%s,%0.0f,%0.2f]",
					($existing_readings > 0 ? ",\n " : ""),
					$r->{timestamp},
					$r->{energy}->[$device-1],
					$r->{temp};
				$existing_readings = 1;
			}
		}
	}
}

printf "]\n";

